--EXEC SP_CYCLE_TIME_REPORT 0,'02/12/2011','02/23/2011','0','0',0,'0'
CREATE PROCEDURE SP_CYCLE_TIME_REPORT
(
  @BASE_PRODUCT_ID INT, 
  @FROM_DATE DATETIME, 
  @TO_DATE DATETIME, 
  @PROCESS_TYPE VARCHAR(50), 
  @VENDOR_NAME VARCHAR(50), 
  @PROCESSSTEP_ID INT,
  @REPORT_TYPE   VARCHAR(20)

)
AS
BEGIN
	DECLARE @QUARTER_NO		INT
	DECLARE @YEAR		INT

	SELECT @YEAR = CC.CC_YEAR, @QUARTER_NO = CQC_QUARTER_NO FROM COMPANY_QUARTERLY_CALENDAR CQC, COMPANY_CALENDAR CC
		WHERE CQC.CQC_YEARID = CC.CC_YEARID 
		AND @TO_DATE >= CQC_START_DATE 
		AND @TO_DATE <= CQC_END_DATE

	DECLARE @WIP_RESULT_STRING NVARCHAR(4000)
	DECLARE @WIP_CURSOR CURSOR
	DECLARE @PROCESSTEP_ID INT, @LOT_ID INT, @PS_TYPE VARCHAR(50), @QTY_IN INT, @QTY_OUT INT
	DECLARE @MERGE_QTY INT , @TOTAL_QTY_IN INT, @SPLIT_QTY INT
	DECLARE @STDCOST NUMERIC(18,4), @OVERHEADCOST NUMERIC(18,4)
	DECLARE @STDYIELD NUMERIC(18,4)

	CREATE TABLE #WIP_RESULTS ( LOT_ID INT, PROCESSSTEP_ID INT, PART_NUMBER VARCHAR(50), LOT_NUMBER VARCHAR(50), PS_TYPE VARCHAR(50), 
				VENDOR_NAME VARCHAR(50), DATE_IN DATETIME, DATE_OUT DATETIME, QTY_IN INT, QTY_OUT INT, ACTUAL_YIELD NUMERIC(18,4), 
				STD_YIELD NUMERIC(18,4), PS_TYPE_ID INT )

	SET @WIP_RESULT_STRING = 
		'INSERT INTO #WIP_RESULTS (LOT_ID, PROCESSSTEP_ID, PART_NUMBER, LOT_NUMBER, PS_TYPE, VENDOR_NAME,DATE_IN,DATE_OUT,PS_TYPE_ID) 
			SELECT DISTINCT LT.LOT_ID, LT.ProcessStep_Id, Part_Number, Lot_Number, Ps_Type, L_VENDOR_NAME,LT.DATE_IN, SHIP_DATE,(SELECT Sorting_Order FROM Process_Types_Table WHERE Process_Type=PS.PS_TYPE)
		 FROM Lot_Table LT, Product P, Process_Step PS, Location L, Shipped_Lot SL
		WHERE LT.ProcessStep_Id = P.ProcessStep_ID AND P.ProcessStep_Id = PS.ProcessStep_ID AND Lt.Location_ID = L.Location_ID AND 
		LT.LOT_ID=SL.LOT_ID AND P.P_Type IS NULL AND Lot_Current_Status = ''Close'' '

	IF @PROCESS_TYPE <> '0' AND @PROCESS_TYPE IS NOT NULL
	BEGIN
		SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND PS.PS_Type = '''+ @PROCESS_TYPE + ''''	
	END
	IF @BASE_PRODUCT_ID > 0 
	BEGIN
		SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID)
	END
	IF @PROCESSSTEP_ID > 0
	BEGIN
		SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND LT.ProcessStep_ID = '+ CONVERT(VARCHAR, @PROCESSSTEP_ID)
	END
	IF @VENDOR_NAME <> '0' AND @VENDOR_NAME IS NOT NULL
	BEGIN
		SET @WIP_RESULT_STRING = @WIP_RESULT_STRING + ' AND L.L_Vendor_Name = '''+ @VENDOR_NAME + ''''
	END
	
	SET @WIP_RESULT_STRING = @WIP_RESULT_STRING	+ ' AND CAST(CONVERT(VARCHAR,SL.SHIP_DATE,101) AS DATETIME) >= '''+ CONVERT(VARCHAR,@FROM_DATE, 101) +''' '
    SET @WIP_RESULT_STRING = @WIP_RESULT_STRING	+ ' AND CAST(CONVERT(VARCHAR,SL.SHIP_DATE,101) AS DATETIME) <= '''+ CONVERT(VARCHAR,@TO_DATE, 101) +''' '
	--PRINT @WIP_RESULT_STRING
	EXEC (@WIP_RESULT_STRING)

	SET @WIP_CURSOR = CURSOR FOR SELECT PROCESSSTEP_ID, LOT_ID, PS_TYPE FROM #WIP_RESULTS
		
	OPEN @WIP_CURSOR
	FETCH NEXT FROM @WIP_CURSOR INTO @PROCESSTEP_ID, @LOT_ID, @PS_TYPE
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @QTY_IN = MAX(QTY_IN) FROM LOT_TABLE_HISTORY WHERE LOT_Id = @LOT_ID
		SELECT @QTY_OUT = SUM(SHIP_QTY) FROM SHIPPED_LOT WHERE LOT_ID = @LOT_ID
		select @MERGE_QTY = ISNULL(CHILD_LOT_QTY1,0) FROM Lot_Merge_table where Parent_lot_id = @LOT_ID
		select @SPLIT_QTY = ISNULL(CHILD_LOT_QTY1,0) FROM Lot_Split_table where Parent_lot_id = @LOT_ID
		SET @TOTAL_QTY_IN = ISNULL(@QTY_IN,0) - ISNULl(@SPLIT_QTY,0) + ISNULL(@MERGE_QTY,0)

		IF @TOTAL_QTY_IN IS NULL OR @TOTAL_QTY_IN = 0
		BEGIN
			SET @TOTAL_QTY_IN = @QTY_OUT
		END 
		SELECT @STDYIELD = (CASE WHEN @QUARTER_NO = 1 THEN Q1_YIELD
										WHEN @QUARTER_NO = 2 THEN Q2_YIELD
										WHEN @QUARTER_NO = 3 THEN Q3_YIELD
										ELSE Q4_YIELD
									 END)
		FROM UPDATE_VIEW_COST_TABLE WHERE PROCESSSTEP_ID = @PROCESSTEP_ID AND YEAR(FIN_YEAR) = @YEAR
		
		UPDATE #WIP_RESULTS SET QTY_IN=@TOTAL_QTY_IN, QTY_OUT=@QTY_OUT,
		ACTUAL_YIELD = CONVERT(NUMERIC(18,4), (CONVERT(NUMERIC(18,4),@QTY_OUT)/CONVERT(NUMERIC(18,4),@TOTAL_QTY_IN))*100 ),
		STD_YIELD = ISNULL(@STDYIELD,100)  WHERE LOT_ID = @LOT_ID
		
		SET @PROCESSTEP_ID = NULL
		SET @LOT_ID = NULL
		SET @PS_TYPE  = NULL
		SET @TOTAL_QTY_IN = NULL
		SET @MERGE_QTY = NULL
		SET @STDYIELD = NULL
		FETCH NEXT FROM @WIP_CURSOR INTO @PROCESSTEP_ID, @LOT_ID, @PS_TYPE
	END
	CLOSE @WIP_CURSOR
	DEALLOCATE @WIP_CURSOR
	
	SELECT PART_NUMBER AS [Part Number], LOT_NUMBER AS [Lot Number], VENDOR_NAME AS VENDOR, PS_TYPE AS PROCESS, QTY_IN AS [QTY IN], QTY_OUT AS [Qty Out], 
		DATE_IN AS [DATE IN],DATE_OUT AS [DATE OUT], (ACTUAL_YIELD/100.00) AS [Actual Yield], (STD_YIELD/100.00) as [Std Yield] , DATEDIFF( DD,DATE_IN,DATE_OUT) AS [Cycle Time] 
		FROM #WIP_RESULTS  WHERE QTY_IN > 0  ORDER BY PS_TYPE_ID, VENDOR_NAME
	
END
